package com.xm.dao.reception_desk;

import com.xm.entity.*;
import com.xm.exception.SqlErrorException;
import com.xm.util.BaseUtil;
import com.xm.util.DiscountConverterUtil;
import org.intellij.lang.annotations.Language;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 商品Dao
 *
 * @author 康文程
 * @date 2023-12-26 下午 03:45
 */
public class CommodityDao extends BaseUtil {
    /**
     * 根据名称模糊查询 商品并且只查询6条数据状态要为正常
     *
     * @return List<Commodity> 匹配的商品列表
     */
    public List<Commodity> searchNameLimitNumber(String name) {
        @Language("sql")
        String sql = "SELECT commodity_id,commodity_name,commodity_images,commodity_price FROM commodity " +
                "WHERE commodity_name LIKE ? and commodity_state <=> 0 order by shelf_time asc LIMIT 6";
        List<Commodity> commodityList = new ArrayList<>();
        ResultSet rs = null;
        try {
            rs = executeQuery(sql, "%" + name + "%");
            while (rs.next()) {
                Commodity commodity = new Commodity();
                commodity.setCommodityId(rs.getInt("commodity_id"));
                commodity.setCommodityName(rs.getString("commodity_name"));
                commodity.setCommodityImages(rs.getString("commodity_images").split(",")[0]);
                commodity.setCommodityPrice(rs.getBigDecimal("commodity_price"));
                commodityList.add(commodity);
            }
        } catch (SqlErrorException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            close(rs);
        }

        return commodityList;
    }

    /**
     * 根据商品种类查询商品的Id和名称还有图片、价格
     *
     * @param classId 商品种类
     * @return List<Commodity> 匹配的商品列表
     */
    public List<Commodity> searchByCommodityClass(Integer classId) {
        @Language("sql")
        String sql = "SELECT commodity_id,commodity_name,commodity_images,commodity_price" +
                " FROM commodity WHERE commodity_class = ? and commodity_state <=> 0 order by shelf_time asc LIMIT 6";

        List<Commodity> commodityList = new ArrayList<>();
        ResultSet rs = null;
        try {
            rs = executeQuery(sql, classId);
            while (rs.next()) {
                Commodity commodity = new Commodity();
                commodity.setCommodityId(rs.getInt("commodity_id"));
                commodity.setCommodityName(rs.getString("commodity_name"));
                commodity.setCommodityImages(rs.getString("commodity_images").split(",")[0]);
                commodity.setCommodityPrice(rs.getBigDecimal("commodity_price"));
                commodityList.add(commodity);
            }
        } catch (SqlErrorException e) {
            e.printStackTrace();
            // 可以根据具体情况处理异常
            // 这里建议抛出自定义的异常，或者进行日志记录等操作
        } catch (SQLException e) {
            throw new RuntimeException(e);
            // 将 SQL 异常转换为运行时异常抛出
        } finally {
            close(rs);
        }

        return commodityList;
    }

    /**
     * 根据商品种类查询商品的Id和名称还有图片
     *
     * @param ids 商品种类Id 最多传两个
     * @return List<Commodity> 匹配的商品列表
     */
    public List<Commodity> searchByCommodityClassId(String ids) {
        //执行多个参数SQL
        @Language("sql")
        String sqlMultipleParameters = "SELECT commodity_id, commodity_name, commodity_images FROM commodity WHERE (commodity_class = ?) " +
                "OR commodity_class = ? AND commodity_state <=> 0 LIMIT 12;";
        //执行单个参数SQL
        @Language("sql")
        String sql = "SELECT commodity_id, commodity_name, commodity_images FROM commodity WHERE (commodity_class = ?) " +
                "AND commodity_state <=> 0 LIMIT 12;";

        List<Commodity> commodityList = new ArrayList<>();
        ResultSet rs = null;
        String multiple = ",";
        try {
            //判断是否存在多个id
            if (ids.contains(multiple)) {
                String[] id = ids.split(",");
                rs = executeQuery(sqlMultipleParameters, Integer.parseInt(id[0]), Integer.parseInt(id[1]));
            } else {
                rs = executeQuery(sql, Integer.parseInt(ids));
            }
            while (rs.next()) {
                Commodity commodity = new Commodity();
                commodity.setCommodityId(rs.getInt("commodity_id"));
                commodity.setCommodityName(rs.getString("commodity_name"));
                commodity.setCommodityImages(rs.getString("commodity_images").split(",")[0]);
                commodityList.add(commodity);
            }
        } catch (SqlErrorException e) {
            e.printStackTrace();
            // 可以根据具体情况处理异常
            // 这里建议抛出自定义的异常，或者进行日志记录等操作
        } catch (SQLException e) {
            throw new RuntimeException(e);
            // 将 SQL 异常转换为运行时异常抛出
        } finally {
            close(rs);
        }

        return commodityList;
    }

    /**
     * 根据商品种类查询商品的Id和名称还有图片、价格、描述
     *
     * @param classId 商品种类
     * @return List<Commodity> 匹配的商品列表
     */
    public List<Commodity> productCategory(Integer classId) {
        @Language("sql")
        String sql = "SELECT commodity_id,commodity_name,commodity_description,commodity_images,commodity_price,commodity_discount " +
                " FROM commodity WHERE commodity_class = ? and commodity_state <=> 0 order by shelf_time asc LIMIT 8";

        List<Commodity> commodityList = new ArrayList<>();
        ResultSet rs = null;
        try {
            rs = executeQuery(sql, classId);
            while (rs.next()) {
                Commodity commodity = new Commodity();
                commodity.setCommodityId(rs.getInt("commodity_id"));
                commodity.setCommodityName(rs.getString("commodity_name"));
                commodity.setCommodityDescription(rs.getString("commodity_description"));
                commodity.setCommodityImages(rs.getString("commodity_images").split(",")[0]);
                commodity.setCommodityPrice(rs.getBigDecimal("commodity_price"));
                commodity.setSubtotal(rs.getInt("commodity_discount") == 0 ? rs.getBigDecimal("commodity_price") :
                        DiscountConverterUtil.calculateDiscount(rs.getBigDecimal("commodity_price"),rs.getInt("commodity_discount")));
                commodityList.add(commodity);
            }
        } catch (SqlErrorException e) {
            e.printStackTrace();
            // 可以根据具体情况处理异常
            // 这里建议抛出自定义的异常，或者进行日志记录等操作
        } catch (SQLException e) {
            throw new RuntimeException(e);
            // 将 SQL 异常转换为运行时异常抛出
        } finally {
            close(rs);
        }

        return commodityList;
    }

    /**
     * 随机查询10条商品数据
     *
     * @return List<Commodity> 随机查询的商品列表
     */
    public List<Commodity> getRandomCommodities() {
        @Language("sql")
        String sql = "SELECT c.commodity_id, c.commodity_images,c.commodity_price" +
                ",c.commodity_name,cc.comments_satisfaction " +
                "FROM commodity c " +
                "LEFT JOIN commodity_comments cc ON c.commodity_id = cc.commodity_id " +
                "WHERE c.commodity_state <=> 0 " +
                "ORDER BY RAND() LIMIT 10 ";

        List<Commodity> commodityList = new ArrayList<>();
        ResultSet rs = null;
        try {
            rs = executeQuery(sql);
            while (rs.next()) {
                Commodity commodity = new Commodity();
                commodity.setCommodityId(rs.getInt("commodity_id"));
                commodity.setCommodityName(rs.getString("commodity_name"));
                commodity.setCommodityImages(rs.getString("commodity_images").split(",")[0]);
                commodity.setCommodityPrice(rs.getBigDecimal("commodity_price"));
                commodity.setCommodityComment(new CommodityComment(rs.getInt("comments_satisfaction")));
                commodityList.add(commodity);
            }
        } catch (SQLException e) {
            throw new RuntimeException("SQL错误", e);
            // 将 SQL 异常转换为运行时异常抛出
        } finally {
            close(rs);
        }

        return commodityList;
    }

    /**
     * 根据商品ID查询商品信息
     *
     * @param commodityId 商品ID
     * @return Commodity 商品对象
     */
    public Commodity searchByCommodityId(Integer commodityId) {
        @Language("sql")
        String sql = "SELECT *" +
                " FROM commodity c " +
                "left join commodity_serve cs on c.commodity_id = cs.commodity_id " +
                "WHERE c.commodity_id = ? and c.commodity_state <=> 0";

        Commodity commodity = null;
        ResultSet rs = null;
        ResultSet rsSpecification = null;
        ResultSet rsSpecificationType = null;
        ResultSet rsInsuranceType = null;
        ResultSet rsInsurance = null;
        try {
            rs = executeQuery(sql, commodityId);
            if (rs.next()) {
                commodity = new Commodity();
                commodity.setCommodityId(rs.getInt("commodity_id"));
                commodity.setCommodityName(rs.getString("commodity_name"));
                commodity.setKeywords(rs.getString("keywords"));
                commodity.setCommodityDiscount(rs.getInt("commodity_discount"));
                commodity.setCommodityImages(rs.getString("commodity_images"));
                commodity.setCommodityPrice(rs.getBigDecimal("commodity_price"));
                CommodityServe commodityServe = new CommodityServe();
                commodityServe.setIsSelfEmployed(rs.getInt("is_self_employed"));
                commodityServe.setIsMiShipments(rs.getInt("is_mi_shipments"));
                commodityServe.setIsReturn(rs.getInt("is_return"));
                commodityServe.setIsPriceProtection(rs.getInt("is_price_protection"));
                commodityServe.setIsShippingInstructions(rs.getInt("is_shipping_instructions"));
                commodityServe.setIsAfterSales(rs.getInt("is_after_sales"));
                commodity.setCommodityServe(commodityServe);

                @Language("SQL")
                String specificationTypeSql = "select distinct cst.specification_type_id,cst.specification_type_name from commodity_specification_type cst" +
                        " left join commodity_specification cs " +
                        "on cst.specification_type_id = cs.specification_type_id " +
                        "where cs.commodity_id = ?";
                rsSpecificationType = executeQuery(specificationTypeSql, commodity.getCommodityId());


                List<CommoditySpecificationType> commoditySpecificationTypeList = new ArrayList<>();
                while (rsSpecificationType.next()) {
                    CommoditySpecificationType commoditySpecificationType = new CommoditySpecificationType();
                    commoditySpecificationType.setSpecificationTypeId(rsSpecificationType.
                            getInt("specification_type_id"));
                    commoditySpecificationType.setSpecificationTypeName(rsSpecificationType.
                            getString("specification_type_name"));

                    @Language("SQL")
                    String specificationSql = "select * from commodity_specification where commodity_id = ? and specification_type_id = ?";
                    rsSpecification = executeQuery(specificationSql, commodity.getCommodityId(),
                            commoditySpecificationType.getSpecificationTypeId());
                    List<CommoditySpecification> specificationList = new ArrayList<>();
                    while (rsSpecification.next()) {
                        CommoditySpecification commoditySpecification = new CommoditySpecification();
                        commoditySpecification.setSpecificationId(rsSpecification.getInt("specification_id"));
                        commoditySpecification.setSpecificationName(rsSpecification.getString("specification_name"));
                        commoditySpecification.setAddPrice(rsSpecification.getBigDecimal("add_price"));
                        specificationList.add(commoditySpecification);
                    }
                    commoditySpecificationType.setCommoditySpecification(specificationList);
                    commoditySpecificationTypeList.add(commoditySpecificationType);
                }
                commodity.setCommoditySpecificationTypeList(commoditySpecificationTypeList);

                @Language("SQL")
                String insuranceTypeSql = "select distinct cit.insurance_type_id,cit.insurance_type_image," +
                        "cit.insurance_type_title,cit.insurance_type_logo from commodity_insurance_type cit " +
                        "left join commodity_insurance ci on cit.insurance_type_id = ci.insurance_type_id " +
                        "where ci.commodity_id = ?";
                rsInsuranceType = executeQuery(insuranceTypeSql, commodity.getCommodityId());
                List<CommodityInsuranceType> commodityInsuranceTypeList = new ArrayList<>();
                while (rsInsuranceType.next()) {
                    CommodityInsuranceType commodityInsuranceType = new CommodityInsuranceType();
                    commodityInsuranceType.setInsuranceTypeId(rsInsuranceType.getInt("insurance_type_id"));
                    commodityInsuranceType.setInsuranceTypeImage(rsInsuranceType.getString("insurance_type_image"));
                    commodityInsuranceType.setInsuranceTypeTitle(rsInsuranceType.getString("insurance_type_title"));
                    commodityInsuranceType.setInsuranceTypeLogo(rsInsuranceType.getString("insurance_type_logo"));


                    @Language("SQL")
                    String insuranceSql = "select * from commodity_insurance where commodity_id = ? and insurance_type_id = ?";
                    rsInsurance = executeQuery(insuranceSql,commodity.getCommodityId(),commodityInsuranceType.getInsuranceTypeId());
                    List<CommodityInsurance> commodityInsuranceList = new ArrayList<>();
                    while (rsInsurance.next()){
                        CommodityInsurance commodityInsurance = new CommodityInsurance();
                        commodityInsurance.setInsuranceId(rsInsurance.getInt("insurance_id"));
                        commodityInsurance.setInsuranceTitle(rsInsurance.getString("insurance_title"));
                        commodityInsurance.setInsurancePreferential(rsInsurance.getBigDecimal("insurance_preferential"));
                        commodityInsurance.setInsuranceIssue(rsInsurance.getString("insurance_issue"));
                        commodityInsurance.setAddPrice(rsInsurance.getBigDecimal("add_price"));
                        commodityInsuranceList.add(commodityInsurance);
                    }
                    commodityInsuranceType.setCommodityInsurances(commodityInsuranceList);
                    commodityInsuranceTypeList.add(commodityInsuranceType);
                }
                commodity.setCommodityInsuranceTypeList(commodityInsuranceTypeList);
            }
        } catch (SqlErrorException e) {
            e.printStackTrace();
            // 可以根据具体情况处理异常
            // 这里建议抛出自定义的异常，或者进行日志记录等操作
        } catch (SQLException e) {
            throw new RuntimeException(e);
            // 将 SQL 异常转换为运行时异常抛出
        } finally {
            close(rs);
            close(rsSpecification);
            close(rsSpecificationType);
            close(rsInsuranceType);
            close(rsInsurance);
        }

        return commodity;
    }

    /**
     * 根据商品名称或关键字模糊查询商品并进行分页，每页显示指定数量的数据
     *
     * @param nameOrKeyword 商品名称或关键字
     * @param pageNumber    当前页码
     * @param pageSize      每页显示数量
     * @param orderBy       排序字段
     * @param isAsc         是否升序
     * @param classId       类别
     * @return Page<Commodity> 分页查询结果
     */
    public Page<Commodity> searchNameOrKeywordWithPagination(String nameOrKeyword, Integer pageNumber,
                                                             Integer pageSize, String orderBy,
                                                             Boolean isAsc, Integer classId) {
        // 构建查询语句
        StringBuilder sqlBuilder = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sqlBuilder.append("SELECT commodity_id,commodity_description,commodity_discount,commodity_name,commodity_images,commodity_price")
                .append(" FROM commodity WHERE commodity_state <=> 0");

        // 添加条件：商品名称或关键字
        if (nameOrKeyword != null && !nameOrKeyword.trim().isEmpty()) {
            sqlBuilder.append(" AND (commodity_name LIKE ? OR keywords LIKE ?)");
            params.add("%" + nameOrKeyword + "%");
            params.add("%" + nameOrKeyword + "%");
        }

        // 添加条件：类别
        if (classId != null && classId != 0) {
            sqlBuilder.append(" AND class_id = ?");
            params.add(classId);
        }

        // 构建排序语句
        if (orderBy != null && !orderBy.trim().isEmpty()) {
            sqlBuilder.append(" ORDER BY ").append(orderBy);
            if (!isAsc) {
                sqlBuilder.append(" DESC");
            }
        }

        // 查询总数
        StringBuilder countSqlBuilder = new StringBuilder();
        countSqlBuilder.append("SELECT COUNT(*) as count FROM commodity WHERE commodity_state <=> 0");
        if (nameOrKeyword != null && !nameOrKeyword.trim().isEmpty()) {
            countSqlBuilder.append(" AND (commodity_name LIKE ? OR keywords LIKE ?)");
        }
        if (classId != null && classId != 0) {
            countSqlBuilder.append(" AND class_id = ?");
        }

        // 执行查询
        List<Commodity> commodityList = new ArrayList<>();
        ResultSet rs = null;
        try {
            // 查询总数
            rs = executeQuery(countSqlBuilder.toString(), params.toArray());
            int count = 0;
            if (rs.next()) {
                count = rs.getInt("count");
            }

            // 构建分页查询语句
            int offset = (pageNumber - 1) * pageSize;
            sqlBuilder.append(" LIMIT ?, ?");
            params.add(offset);
            params.add(pageSize);

            // 执行分页查询
            rs = executeQuery(sqlBuilder.toString(), params.toArray());
            while (rs.next()) {
                Commodity commodity = new Commodity();
                commodity.setCommodityId(rs.getInt("commodity_id"));
                commodity.setCommodityName(rs.getString("commodity_name"));
                commodity.setCommodityDescription(rs.getString("commodity_description"));
                commodity.setCommodityImages(rs.getString("commodity_images").split(",")[0]);
                commodity.setCommodityPrice(rs.getBigDecimal("commodity_price"));
                commodity.setSubtotal(rs.getInt("commodity_discount") == 0 ?
                        rs.getBigDecimal("commodity_price") :
                        DiscountConverterUtil.calculateDiscount(rs.getBigDecimal("commodity_price"),
                                rs.getInt("commodity_discount")));
                commodityList.add(commodity);
            }

            // 构建分页结果对象
            Page<Commodity> page = new Page<>();
            page.setData(commodityList);
            page.setPageNumber(pageNumber);
            page.setPageSize(pageSize);
            page.setCount(count);
            page.setTotalPages(pageSize);

            return page;
        } catch (SqlErrorException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            close(rs);
        }

        return null;
    }
}
